package ca.uvic.group2.api;

import java.io.Serializable;

public class DBSetup implements Serializable {
    public static final String GROUP_DB = "se462g02";

    public static final String createUsersTbl =
        "CREATE TABLE users ( userid serial, name varchar(20) UNIQUE, password varchar(10), PRIMARY KEY(userid));" +
        "CREATE INDEX user_id_index ON users USING BTREE (userid); " +
        "CREATE INDEX user_name_index ON users USING BTREE (name); ";

    public static final String createDollarActsTbl =
        "CREATE TABLE dollarAccounts(daccountid serial,userid int, balance money, PRIMARY KEY(daccountid)); " +
        "CREATE INDEX dollar_user_index ON dollaraccounts USING BTREE (userid); ";

    //public static final String createStockActsTbl =
    //    "CREATE TABLE stockAccounts(saccountid  serial,userid int,stocksym char(3),stock_balance int, PRIMARY KEY (saccountid)); ";

    public static String createStockTable(int num) {
        String tableName = "stockaccounts" + num;
        return  "CREATE TABLE " + tableName +
            "(saccountid serial,userid int,stocksym char(3),stock_balance int, PRIMARY KEY (saccountid)); " +
            "CREATE INDEX stock_multi_index" + num + " on " + tableName + "(userid, stocksym);";
    }

    public static final String createTriggersTbl =
        "CREATE TABLE triggers(triggerid serial, username varchar(20), kind int, stocksym varchar(10), " +
        "stock_amount int, stock_price money, dollar_amount money, PRIMARY KEY (triggerid)); " +
        "CREATE INDEX trigger_index ON triggers (triggerid);";

//     public static final String createIndexes =
//         "CREATE INDEX trigger_index on Triggers (triggerid);"
//         + "CREATE INDEX stock_multi_index on stockAccounts (userid, stocksym);"
//         + "CREATE INDEX dollar_user_index on dollarAccounts using btree (userid);"
//         + "CREATE INDEX user_id_index on users using btree (userid);"
//         + "CREATE INDEX user_name_index on users using btree (name);";

    public static final String drop_db =
        "drop table users; drop table dollaraccounts; drop table triggers;";

//     public static final String drop_indexes = "drop index stock_multi_index"; // the rest are automatically dropped
//         //"drop index user_id_index; drop index dollar_user_index; drop index stock_multi_index; " +
//         //"drop index trigger_index; drop index user_name_index;";


    public static String dropStockTable(int num) {
        return "DROP TABLE stockAccounts" + num + ";";
    }

}
